package ESP.Dao;


import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import ESP.Dao.Impl.IADao;
import ESP.Dao.Impl.IBDao;
import ESP.Dao.Impl.IBaseDao;
import ESP.Dao.Impl.ICDao;
import ESP.Dao.Impl.IDDao;
import ESP.Dao.Impl.IEDao;
import ESP.Dao.Impl.ILDao;
import ESP.Util.Encryption;
import ESP.Util.IDFactory;
import ESP.Util.RemoveFile;
import ESP.VO.Data;
import ESP.VO.Role;
import ESP.VO.User;

import com.alibaba.fastjson.JSONArray;

@Component
public class BaseDao extends IBaseDao implements IADao,IBDao,ICDao,IDDao,IEDao,ILDao{
	@Resource
	public void setJb(JdbcTemplate jb) {
		super.setJdbcTemplate(jb);
	}
	@SuppressWarnings("finally")
	@Override
	public String getRoleByUser_id(String user_id) {
		String user_role_id = "";
		sql = "select user_role_id from esp_user where user_id=?";
		try {
			Map map = getJdbcTemplate().queryForMap(sql, new Object[]{user_id});
			if (map.isEmpty() || map.size() != 1) {
				user_role_id = "";
			}else {
				user_role_id = (String)map.get("user_role_id");
			}
		} catch (Exception e) {
			user_role_id = "";
		}finally {
			return user_role_id;
		}
	}
	@Override
	public User login(String user_id, String user_password) {
		User user = null;
		sqlBuffer = new StringBuffer("select * from esp_user where ")
		.append("user_id = ? and ").append("user_password = ?");
		
		Map<String, Object> map;
		try{
			map = getJdbcTemplate().queryForMap(sqlBuffer.toString(), new Object[] { user_id, user_password });
			logger.info("登录成功>>>>" + user_id);
		}catch(Exception e){
			logger.info("登录异常>>>>" + user_id);
			return null;
		}
		user = new User();
		user.setUser_id((String) map.get("user_id"));
		user.setUser_name((String) map.get("user_name"));
		user.setUser_password((String) map.get("user_password"));
		user.setUser_role_id((String) map.get("user_role_id"));
		logger.info("保存会话信息到session成功>>>>"+user.getUser_name());
		sqlBuffer = null;
		return user;
	}
	@Override
	public String checkForgetPsw(String user_id, String user_input_email){
		logger.info("忘记密码输入校验>>>>"+user_id+"/"+user_input_email);
		String addressTo = "";
		Map map;
		String user_role_id = this.getRoleByUser_id(user_id);
		if (user_role_id.trim().equals("")) {
			return addressTo;
		}else if(user_role_id.equals(Role.ADMIN) || user_role_id.equals(Role.TEACHER)){
			sql = "select t.teacher_eamil user_email from esp_user u, esp_teacher t where u.user_id=t.teacher_id and t.teacher_id=? and t.teacher_eamil=?";
		}else {
			sql = "select s.stu_eamil user_email from esp_user u,esp_student s where u.user_id=s.stu_id and s.stu_id=? and s.stu_eamil=?";
		}
		logger.info("checkForgetPsw>>确认输入信息>>>>"+sql);
		try {
			map = getJdbcTemplate().queryForMap(sql, new Object[]{user_id, user_input_email});
			if (map.size() != 1 || map.isEmpty() || map.get("user_email") == null || map.get("user_email").equals("")) {
				logger.info("checkForgetPsw>>>>用户不存在，或用户没有更新注册邮箱");
				return "";
			}else {
				logger.info("checkForgetPsw>>>>已经成功获取用户注册邮箱");
				return (String) map.get("user_email");
			}
		} catch (Exception e) {
			logger.info("checkForgetPsw>>>>："+e.getMessage());
			return "";
		}
	}
	@Override
	public int forgetPsw(String user_id, String newPassword){
		int temp = 0;
		String user_role_id = this.getRoleByUser_id(user_id);
		if (user_role_id.equals("")) {
			return temp;
		}else {
			sql = "update esp_user set user_password=? where user_id=?";
		}
		temp = getJdbcTemplate().update(sql, new Object[]{Encryption.encryptionSHA1(newPassword), user_id});
		return temp ;
	}
	@Override
	public JSONArray toJson(List<Map<String,Object>> list){
		JSONArray json = (JSONArray) JSONArray.toJSON(list);
		list = null;
		return json;
	}
	@Override
	public JSONArray toJson(Map<String, Object> map){
		JSONArray json = (JSONArray) JSONArray.toJSON(map);
		map = null;
		return json;
	}
	@Override
	public Map<String, String> setReturnInfo(Map<String, String> message, String returnFlag, String returnMsg){
		if (message == null) {
			message = new HashMap<String, String>();
		}
		message.clear();
		message.put("returnFlag", returnFlag);
		message.put("returnMsg", returnMsg);
		logger.info("returnFlag is :"+returnFlag+">>and returnMsg is :"+returnMsg);
		return message;
	}
	//====================a02
	@Override
	public List<Map<String,Object>> queryUser(String user_id){
		List<Map<String,Object>> list;
		sqlBuffer = new StringBuffer("select u.user_id,u.user_name from esp_user u where u.user_id is not null");
		try {
			if (user_id != null && !user_id.equals("")) {
				sqlBuffer.append(" and u.user_id=?");
				list = getJdbcTemplate().queryForList(sqlBuffer.toString(), new Object[]{user_id});
			}else {
				list = getJdbcTemplate().queryForList(sqlBuffer.toString());
			}
			logger.info("queryUser>>sqlBuffer>>"+sqlBuffer);
		} catch (Exception e) {
			logger.info("queryUser>>出现异常："+e.getMessage());
			list = null;
		}
		return list;
		
	}
//	********************************************************************************************A03角色分配
	@Override
	public List<Map<String,Object>> queryStudentByRole(String user_id, String user_role_id,
			String stu_id,String stu_name,String stu_role_id,String stu_major_id,
			String stu_faculty_id,String stu_grade,String stu_class,String stu_groupstate){
		List<Map<String,Object>> list = null;
		sqlBuffer = new StringBuffer("select u.user_id,u.user_name,u.user_role_id,r.role_name," +
				"s.stu_faculty_id,f.faculty_name,s.stu_major_id,m.major_name,s.stu_grade," +
				"s.stu_class,s.stu_group_id,s.stu_sex,s.stu_phone,s.stu_qq,s.stu_eamil,s.stu_note from esp_user u,esp_student s,esp_role r," +
				"esp_faculty f,esp_major m where u.USER_ID=s.STU_ID and r.ROLE_ID=u.USER_ROLE_ID" +
				" and f.FACULTY_ID=s.STU_FACULTY_ID and m.MAJOR_ID=s.STU_MAJOR_ID ");
		if (!user_role_id.equals(Role.ADMIN) && !user_role_id.equals(Role.TEACHER)) {//学生
			Map<String,Object> map = this.getUserInfo(user_id);
			sqlBuffer.append(" and s.stu_grade=? and s.stu_faculty_id=? and s.stu_major_id=? and s.stu_class=? ");
			if (stu_id != null && !stu_id.trim().equals("")) {
				sqlBuffer.append(" and s.stu_id='"+stu_id+"'");
			}
			if (stu_name != null && !stu_name.trim().equals("")) {
				sqlBuffer.append(" and u.user_name='"+stu_name+"'");
			}
			if (stu_role_id != null && !stu_role_id.trim().equals("")) {
				sqlBuffer.append(" and u.user_role_id='"+stu_role_id+"'");
			}
			if (stu_groupstate != null && stu_groupstate.trim().equals("Y")) {//已经被分组
				sqlBuffer.append(" and stu_group_id is not null and stu_group_id !=''");
			}
			if (stu_groupstate != null && stu_groupstate.trim().equals("N")) {//未被分组
				sqlBuffer.append(" and stu_group_id is null or stu_group_id=''");
			}
			list = getJdbcTemplate().queryForList(sqlBuffer.toString(), new Object[]{(String)map.get("stu_grade"),
				(String)map.get("stu_faculty_id"), (String)map.get("stu_major_id"), (String)map.get("stu_class")});
			logger.info("queryStudentByRole学生>>sqlBuffer:"+sqlBuffer+user_id);
			logger.info("queryStudentByRole学生>>list:"+list);
			return list;
		}else {//教师 管理员
			if (stu_id != null && !stu_id.trim().equals("")) {
				sqlBuffer.append(" and s.stu_id='"+stu_id+"'");
			}
			if (stu_name != null && !stu_name.trim().equals("")) {
				sqlBuffer.append(" and u.user_name='"+stu_name+"'");
			}
			if (stu_role_id != null && !stu_role_id.trim().equals("")) {
				sqlBuffer.append(" and u.user_role_id='"+stu_role_id+"'");
			}
			if (stu_major_id != null && !stu_major_id.trim().equals("")) {
				sqlBuffer.append(" and stu_major_id='"+stu_major_id+"'");
			}
			if (stu_faculty_id != null && !stu_faculty_id.trim().equals("")) {
				sqlBuffer.append(" and stu_faculty_id='"+stu_faculty_id+"'");
			}
			if (stu_grade != null && !stu_grade.trim().equals("")) {
				sqlBuffer.append(" and stu_grade='"+stu_grade+"'");
			}
			if (stu_class != null && !stu_class.trim().equals("")) {
				sqlBuffer.append(" and stu_class='"+stu_class+"'");
			}
			if (stu_groupstate != null && stu_groupstate.trim().equals("Y")) {//已经被分组
				sqlBuffer.append(" and stu_group_id is not null and stu_group_id !=''");
			}
			if (stu_groupstate != null && stu_groupstate.trim().equals("N")) {//未被分组
				sqlBuffer.append(" and stu_group_id is null or stu_group_id=''");
			}
			list = getJdbcTemplate().queryForList(sqlBuffer.toString());
			logger.info("queryStudentByRole教师>>sqlBuffer:"+sqlBuffer);
			logger.info("queryStudentByRole教师>>list:"+list);
			return list;
		}
	}
	private Map<String, Object> getUserInfo(String user_id){
		Map<String, Object> map;
		sql = "select s.stu_grade,s.stu_major_id,s.stu_faculty_id,s.stu_class,u.user_role_id " 
			+ "from esp_user u,esp_student s where u.user_id=? and u.user_id=s.stu_id";
		map = getJdbcTemplate().queryForMap(sql, new Object[]{user_id});
		return map;
	}
	@Override
	public int clearGroupInfo(String stu_id){
		int temp = 0;
		sql = "update esp_student set stu_group_id=null where stu_id=?";
		temp = getJdbcTemplate().update(sql, new Object[]{stu_id});
		logger.info("clearGroupInfo>>sql:"+sql);
		logger.info("clearGroupInfo>>temp:"+temp);
		return temp;
	}
	@Override
	public int confirmRole(String stu_id, String stu_role_id){//待分配学生学号，和待分配角色
		int temp = 0;
		//以下三类角色无法分配
		if (stu_role_id.equals(Role.ADMIN) || stu_role_id.equals(Role.TEACHER) || stu_role_id.equals(Role.LEADERSUPER)) {
			return temp;
		}
		//如果分配学委的角色给原本是组长的学生
		if (stu_role_id.equals(Role.XUEWEI) && this.getUserInfo(stu_id).get("user_role_id").equals(Role.LEADER)) {
			stu_role_id = Role.LEADERSUPER;
		}
		//如果分配组长的角色给原本是学委的学生
		if (stu_role_id.equals(Role.LEADER) && this.getUserInfo(stu_id).get("user_role_id").equals(Role.XUEWEI)) {
			stu_role_id = Role.LEADERSUPER;
		}
		sql = "update esp_user set user_role_id=? where user_id=?";
		temp = getJdbcTemplate().update(sql, new Object[]{stu_role_id, stu_id});
		logger.info("confirmRole>>sql:"+sql);
		logger.info("confirmRole>>temp:"+temp);
		return temp;
	}
	@Override
	public int checkGroupHaveLeader(String stu_group_id){
		int temp;
		sql = "select count(*) from esp_user u,esp_student s where s.stu_id=u.user_id and s.stu_group_id=? "
			+ "and u.user_role_id in (3,6)";
		temp = getJdbcTemplate().queryForInt(sql, new Object[]{stu_group_id});
		logger.info("checkGroupHaveLeader>>sql:"+sql);
		logger.info("checkGroupHaveLeader>>temp:"+temp);
		return temp;
	}
	@Override
	public int clearXuewei(String stu_grade, String stu_faculty_id, String stu_major_id, String stu_class){
		int temp = 0;
		Map<String,Object> map = this.checkHaveXuewei(stu_grade, stu_faculty_id, stu_major_id, stu_class);
		if (map.size() == 0 || map.isEmpty() || map == null) {
			return temp;
		}
		String userId = (String) map.get("USER_ID");
		String userRoleId = (String) map.get("USER_ROLE_ID");
		sql = "update esp_user set user_role_id=? where user_id=?";
		if (userRoleId.equals(Role.LEADERSUPER)) {//如果之前是学委组长
			temp = getJdbcTemplate().update(sql, new Object[]{Role.LEADER, userId});
		}else if (userRoleId.equals(Role.XUEWEI)) {
			temp = getJdbcTemplate().update(sql,  new Object[]{Role.STUDENT, userId});
		}
		logger.info("clearXuewei>>sql:"+sql);
		logger.info("clearXuewei>>temp:"+temp);
		return temp;
	}
	@Override
	public Map<String, Object> checkHaveXuewei(String stu_grade, String stu_faculty_id, String stu_major_id, String stu_class){
		Map<String, Object> map;
		sql = "select u.* from esp_user u,esp_student s where u.user_id=s.stu_id and u.user_role_id in (2,6) "
			+ "and s.stu_grade=? and s.stu_faculty_id=? and s.stu_major_id=? and stu_class=?";
		map = getJdbcTemplate().queryForMap(sql, new Object[]{stu_grade, stu_faculty_id, stu_major_id, stu_class});
		logger.info("checkHaveLeaderSuper>>sql:"+sql);
		logger.info("checkHaveLeaderSuper>>map:"+map);
		return map;
	}
//	********************************************************************************************C01
	@Override
	public List<Map<String,Object>> queryTask(String taskId, String taskName, String taskNote,
			String taskFacultyId, String taskMajorId, int taskYearStart, int taskYearEnd){
		sqlBuffer = null;
		sqlBuffer = new StringBuffer("SELECT t.task_id,t.task_name,t.task_note,t.task_major_id,t.task_faculty_id,f.faculty_name,m.major_name,t.task_year from esp_task t,esp_faculty f,esp_major m " +
				"WHERE t.TASK_FACULTY_ID=f.FACULTY_ID and t.TASK_MAJOR_ID=m.MAJOR_ID");//查询全部
		if(taskId != null && !taskId.equals("")){
			sqlBuffer.append(" and task_id='"+taskId+"'");
		}
		if(taskName != null && !taskName.equals("")){
			sqlBuffer.append(" and task_name like '%"+taskName+"%'");
		}
		if(taskNote != null && !taskNote.equals("")){
			sqlBuffer.append(" and task_note like '%"+taskNote+"%'");
		}
		if(taskFacultyId != null && !taskFacultyId.equals("")){
			sqlBuffer.append(" and f.faculty_id='"+taskFacultyId+"'");
		}
		if(taskMajorId != null && !taskMajorId.equals("")){
			sqlBuffer.append(" and m.major_id='"+taskMajorId+"'");
		}
		int year = new Date().getYear()+1900;
		if(taskYearStart == 0){
			taskYearStart = year;
		}
		if(taskYearEnd == 0){
			taskYearEnd = year+1;
		}
		sqlBuffer.append(" and task_year between "+taskYearStart+" and "+taskYearEnd);
		List<Map<String,Object>> tasklist = getJdbcTemplate().queryForList(sqlBuffer.toString());
		logger.info("queryTask>>sqlBuffer:"+sqlBuffer);
		return tasklist;
	}
	@Override
	public int addTask(String task_id, String task_name, String task_note, String task_major_id,
			String task_faculty_id, int task_year){
		sql = "INSERT INTO esp_task (task_id,task_name,task_note,task_major_id,task_faculty_id,task_year) " +
				" VALUES (?,?,?,?,?,?)";
		int temp = getJdbcTemplate().update(sql, new Object[]{task_id,task_name,task_note,task_major_id,task_faculty_id,task_year});
		logger.info("addTask>>sql:"+sql);
		return temp;
	}
	@Override
	@SuppressWarnings("finally")
	public int removeTask(String task_id){
		sql = "delete from esp_task where task_id = ?";
		int temp = 0;
		try {
			temp = getJdbcTemplate().update(sql, new Object[]{task_id});
			logger.info("removeTask>>sql:"+sql);
		} catch (Exception e) {
			logger.info("removeTask异常>>"+e.getMessage());
		}finally{
			return temp;
		}
	}
	@Override
	public int updateTask(String task_id, String task_name, String task_note, String task_major_id,
			String task_faculty_id, int task_year){
		sql = "update esp_task set " +
				"task_name=?,task_note=?,task_major_id=?,task_faculty_id=?,task_year=? " +
				"where task_id = ?";
		int temp = getJdbcTemplate().update(sql, new Object[]{task_name,task_note,task_major_id,task_faculty_id,task_year,task_id});
		logger.info("updateTask>>sql:"+sql);
		return temp;
	}
	@Override
	public Map<String,Object> queryStu(String user_id){
		sql = "select * from esp_student s,esp_user u where u.user_id=s.stu_id and s.stu_id=?";
		Map<String,Object> stuMap = getJdbcTemplate().queryForMap(sql, new Object[]{user_id});
		logger.info("queryStu>>sql:"+sql);
		logger.info("queryStu>>stuMap:"+stuMap);
		return stuMap;
	}
	@Override
	public int addGroup(String stu_id, String group_task_id){
		int temp = 0;
		//插入赋值
		String group_id = "G"+stu_id;
		String group_name = this.queryGroupName(stu_id, group_task_id);
		Date group_cre_time = new Date(IDFactory.getSqlTime()[0], IDFactory.getSqlTime()[1], IDFactory.getSqlTime()[2]);
		sql = "insert into esp_group (group_id,group_name,group_task_id,group_cre_time,group_note)" +
				" values (?,?,?,?,?)";
		temp = getJdbcTemplate().update(sql, new Object[]{group_id,group_name,group_task_id,group_cre_time,null});
		logger.info("addGroup>>sql:"+sql);
		logger.info("addGroup>>temp:"+temp);
		return temp;
	}
	/**
	 * 通过查询 连接字符串 给group_name赋值
	 * @category select * from esp_user u,esp_student s,esp_faculty f,esp_major m 
	 */
	private String queryGroupName(String stu_id, String group_task_id){
		Map<String, Object> map;
		sql = "select * from " +
			"esp_user u,esp_student s,esp_faculty f,esp_major m " +
			"where u.USER_ID=s.STU_ID and s.STU_FACULTY_ID=f.FACULTY_ID " +
			"and s.STU_MAJOR_ID=m.MAJOR_ID and s.stu_id=?";
		map = getJdbcTemplate().queryForMap(sql, new Object[]{stu_id});
		if (map.isEmpty()) {
			throw new RuntimeException("无法给小组命名，请检查是否登录！或者请稍后重试！");
		}
		String groupName = IDFactory.getGroupName(map.get("STU_GRADE").toString(), 
				map.get("MAJOR_NAME").toString(), map.get("STU_CLASS").toString(), 
				map.get("USER_NAME").toString(), group_task_id, 
				null);
		logger.info("queryGroupName>>sql:"+sql);
		logger.info("queryGroupName>>groupName:"+groupName);
		return groupName;
	}
	@Override
	public Map<String, Object> queryTaskGroup(String user_id){
		sql = "select t.*,g.*,s.*,u.* from esp_student s,esp_group g,esp_task t,esp_user u " +
				"where u.user_id=s.stu_id and s.stu_group_id=g.group_id and g.group_task_id=t.task_id and s.stu_id=?";
		Map<String , Object> map;
		try {
			map = getJdbcTemplate().queryForMap(sql, new Object[]{user_id});
		} catch (Exception e) {
			return null;
		}
		logger.info("queryTaskGroup>>sql:"+sql);
		logger.info("queryTaskGroup>>stuMap:"+map);
		return map;
	}
	@Override
	public int updateGroupTaskId(String stu_id, String group_task_id){
		int cache = this.addGroup(stu_id, group_task_id);
		int temp = 0;
		int sys = 0;
		String groupId = "G"+stu_id;
		if (cache == 1) {
			sql = "update esp_group set group_task_id=? where group_id=?";
			logger.info("updateGroupTaskId->update esp_group>>sql:"+sql);
			temp = getJdbcTemplate().update(sql, new Object[]{group_task_id, groupId});
		}
		if (temp == 1) {
			sql = "update esp_student set stu_group_id=? where stu_id=?";
			logger.info("updateGroupTaskId->update esp_student>>sql:"+sql);
			sys = getJdbcTemplate().update(sql, new Object[]{groupId, stu_id});
		}
		return sys;
	}
	@Override
	public int conformTeamMember(String user_id, String stu_id){
		int temp = 0;
		String user_group_id = this.queryStu(user_id).get("STU_GROUP_ID").toString();
		Map<String,Object> map = this.queryStu(stu_id);
		if (map.get("STU_GROUP_ID") == null || map.get("STU_GROUP_ID").equals("")) {
			sql = "update esp_student set stu_group_id=? where stu_id=?";
			temp = getJdbcTemplate().update(sql, new Object[]{user_group_id, stu_id});
		}else {
			sql = "此学生已经被分组";
		}
		logger.info("conformTeamMember>>sql:"+sql);
		logger.info("conformTeamMember>>temp:"+temp);
		return temp;
	}
//	 * ======================================================================================c02/C03
	@Override
	public List<Map<String,Object>> queryPlanHeadC02(String user_id, String user_role_id,
		String stuId, String stuName, String groupName, String taskId, String majorId, String facultyId){//此行参数对应查询框

		List<Map<String,Object>> list = null;
		sqlBuffer = null;
		sqlBuffer = new StringBuffer("select * from " +
				"esp_user u,esp_student s,esp_faculty f,esp_major m,esp_group g,esp_task t " +
				"where u.USER_ID=s.STU_ID and s.STU_FACULTY_ID=f.FACULTY_ID " +
				"and s.STU_MAJOR_ID=m.MAJOR_ID and s.STU_GROUP_ID=g.GROUP_ID " +
				"and g.GROUP_TASK_ID=t.TASK_ID ");
		if (user_role_id.equals(Role.ADMIN) || user_role_id.equals(Role.TEACHER)) {//如果不是学生
			if(stuId != null && !stuId.equals("")){
				sqlBuffer.append(" and s.STU_ID='"+stuId+"'");
			}
			if(stuName != null && !stuName.equals("")){
				sqlBuffer.append(" and u.user_name='"+stuName+"'");
			}
			if(groupName != null && !groupName.equals("")){
				sqlBuffer.append(" and g.group_name='"+groupName+"'");
			}
			if(taskId != null && !taskId.equals("")){
				sqlBuffer.append(" and t.task_id='"+taskId+"'");
			}
			if(majorId != null && !majorId.equals("")){
				sqlBuffer.append(" and m.major_id='"+majorId+"'");
			}
			if(facultyId != null && !facultyId.equals("")){
				sqlBuffer.append(" and f.faculty_id='"+facultyId+"'");
			}
			list = getJdbcTemplate().queryForList(sqlBuffer.toString());
		}else {//如果是学生
			sqlBuffer.append("and s.STU_ID=?");
			list = getJdbcTemplate().queryForList(sqlBuffer.toString(), new Object[]{user_id});
		}
		logger.info("queryPlanHeadC02>>sqlBuffer:"+sqlBuffer+user_id);
		logger.info("queryPlanHeadC02>>list:"+list);
		return list;
	}
	@Override
	public List<Map<String,Object>> queryPlanBodyC02(String stu_id){
		sql = "select p.plan_id,p.plan_stu_id,p.plan_week,p.plan_expect,p.plan_done," +
				"p.plan_cre_time,p.plan_attendance,p.plan_contribution,p.plan_note" +
				" from esp_plan p where p.PLAN_STU_ID and p.PLAN_GROUP_FLAG != 'Y'" +
				" and p.PLAN_STU_ID=? order by p.plan_week";
		List<Map<String,Object>> list = getJdbcTemplate().queryForList(sql, new Object[]{stu_id});
		logger.info("queryPlanBodyC02>>sql:"+sql+stu_id);
		logger.info("queryPlanBodyC02>>list:"+list);
		return list;
	}
	@Override
	public List<Map<String,Object>> queryPlanHeadC03(String user_id, String user_role_id,
			String stuId, String stuName, String groupName, String taskId, String majorId, String facultyId){//此行参数对应查询框
			List<Map<String,Object>> list = null;
			sqlBuffer = null;
			sqlBuffer = new StringBuffer("select * from esp_user u,esp_student s,esp_faculty f," +
					"esp_major m,esp_group g,esp_task t" +
					" where u.USER_ID=s.STU_ID and u.USER_ROLE_ID in(3,6)" +
					" and s.STU_FACULTY_ID=f.FACULTY_ID and s.STU_MAJOR_ID=m.MAJOR_ID" +
					" and s.STU_GROUP_ID=g.GROUP_ID and g.GROUP_TASK_ID=t.TASK_ID");
			if (user_role_id.equals(Role.ADMIN) || user_role_id.equals(Role.TEACHER)) {//如果不是学生
				if(stuId != null && !stuId.equals("")){
					sqlBuffer.append(" and s.STU_ID='"+stuId+"'");
				}
				if(stuName != null && !stuName.equals("")){
					sqlBuffer.append(" and u.user_name='"+stuName+"'");
				}
				if(groupName != null && !groupName.equals("")){
					sqlBuffer.append(" and g.group_name='"+groupName+"'");
				}
				if(taskId != null && !taskId.equals("")){
					sqlBuffer.append(" and t.task_id='"+taskId+"'");
				}
				if(majorId != null && !majorId.equals("")){
					sqlBuffer.append(" and m.major_id='"+majorId+"'");
				}
				if(facultyId != null && !facultyId.equals("")){
					sqlBuffer.append(" and f.faculty_id='"+facultyId+"'");
				}
				list = getJdbcTemplate().queryForList(sqlBuffer.toString());
			}else {//如果是组长 或者 学委组长
				sqlBuffer.append(" and s.STU_ID=?");
				list = getJdbcTemplate().queryForList(sqlBuffer.toString(), new Object[]{user_id});
			}
			logger.info("queryPlanHeadC03>>sqlBuffer:"+sqlBuffer+user_id);
			logger.info("queryPlanHeadC03>>list:"+list);
			return list;
		}
	@Override
	public List<Map<String,Object>> queryPlanBodyC03(String stu_id){
		sql = "SELECT * from esp_student s LEFT JOIN esp_user u ON u.USER_ID=s.STU_ID" +
				" WHERE s.STU_GROUP_ID=(select s1.STU_GROUP_ID from esp_student s1 where s1.STU_ID=?)";
		List<Map<String,Object>> list = getJdbcTemplate().queryForList(sql, new Object[]{stu_id});
		logger.info("queryPlanBodyC03>>sql:"+sql+stu_id);
		logger.info("queryPlanBodyC03>>list:"+list);
		return list;
	}
	@Override
	public List<Map<String,Object>> queryPlanFootC03(String stu_id){
		sql = "select p.plan_id,p.plan_stu_id,p.plan_week,p.plan_expect,p.plan_done,"
				+ "p.plan_cre_time,p.plan_attendance,p.plan_contribution,p.plan_note"
				+ " from esp_plan p where p.PLAN_STU_ID and p.PLAN_GROUP_FLAG='Y'"
				+ " and p.PLAN_STU_ID=?";
		List<Map<String,Object>> list = getJdbcTemplate()
				.queryForList(sql, new Object[] { stu_id });
		logger.info("queryPlanFootC03>>sql:" + sql + stu_id);
		logger.info("queryPlanFootC03>>list:" + list);
		return list;
	}
	@Override
	public int updateMissionC03(String stu_id, String stu_mission){
		sql = "update esp_student set stu_mission=? where stu_id=?";
		int temp = getJdbcTemplate().update(sql, new Object[]{stu_mission, stu_id});
		logger.info("updateMissionC03>>sql:" + sql + stu_id+"&"+stu_mission);
		logger.info("updateMissionC03>>temp:" + temp);
		return temp;
	}
	@Override
	public int addPlan(String planId, String planStuId, String planWeek, String planExpect,
			String planDone, Date planCreTime, String planGroupFlag,
			String planAttendance, String planContribution, String planNote){
		String sql = "INSERT INTO esp_plan " +
				"(plan_id,plan_stu_id,plan_week,plan_expect,plan_done,plan_cre_time,plan_group_flag," +
				"plan_attendance,plan_contribution,plan_note) VALUES (?,?,?,?,?,?,?,?,?,?)";
		int temp = 0;
		getJdbcTemplate().update(sql, new Object[]{planId,planStuId,planWeek,planExpect,
			planDone,planCreTime,planGroupFlag,planAttendance,planContribution,planNote});
		logger.info("addPlan>>sql:" + sql);
		logger.info("addPlan>>temp:" + temp);
		return temp;
	}
	@Override
	public int checkPlanWeek(String stuId, String planWeek, String planGroupGlag){
		sql = "select count(*) from esp_plan where plan_stu_id=? and plan_week=? and plan_group_flag=?";
		int temp = getJdbcTemplate().queryForInt(sql, new Object[]{stuId, planWeek, planGroupGlag});
		return temp;
	}
	@Override
	public int updatePlan(String planId, String planWeek, String planExpect,String planDone,
			Date planCreTime,String planAttendance, String planContribution, String planNote){
		sql = "update esp_plan set plan_week=?,plan_expect=?,plan_done=?,plan_cre_time=?,plan_attendance=?" +
				",plan_contribution=?,plan_note=? where plan_id=?";
		int temp = getJdbcTemplate().update(sql, new Object[]{planWeek,planExpect,
				planDone,planCreTime,planAttendance,planContribution,planNote,planId});
		logger.info("updatePlan>>sql:"+sql);
		logger.info("updatePlan>>temp:"+temp);
		return temp;
	}
	@Override
	public int removePlan(String planId){
		sql = "delete from esp_plan where plan_id = ?";
		int temp = getJdbcTemplate().update(sql, new Object[]{planId});
		logger.info("removePlan>>sql:"+sql);
		logger.info("removePlan>>temp:"+temp);
		return temp;
	}
//	 * =======================================================================================B 数据管理
	/**
	 * 查询指定文件，用来删除/更新上传的文件
	 */
	private Map<String, Object> queryOneData(String data_id){
		Map<String, Object> map = null;
		sqlBuffer = new StringBuffer("select d.data_id,d.data_owner_id,d.data_path,d.data_type_id" 
				+ " from esp_data d where d.DATA_ID=?");
		try {
			map = getJdbcTemplate().queryForMap(sqlBuffer.toString(), new Object[]{data_id});
		} catch (Exception e) {
			return null;
		}
		return map;
	}
	@Override
	public boolean deleteFile(String dataId){
		Map<String, Object> map = this.queryOneData(dataId);
		if (map == null || map.isEmpty()) {
			return false;
		}
		String delpath = (String) map.get("data_path");//获取删除文件的地址
		logger.info("删除文件地址："+RemoveFile.pathHome+delpath);
		return RemoveFile.deletefile(RemoveFile.pathHome+delpath);//删除指定路径下的文件
	}
	@Override
	public List<Map<String,Object>> queryDataHeadB01(String userRoleId, String stuId, String userName, Date dataCretime1, 
			Date dataCretime2, String dataReportFlag, String typeId){
		List<Map<String,Object>> list = null;
		sqlBuffer = new StringBuffer("select * from" 
			+" (select * from esp_user u,esp_student s where u.USER_ID=s.STU_ID ORDER BY u.USER_ID) a" 
			+" LEFT OUTER JOIN" 
			+" (select d.data_id,d.data_cretime,d.data_flag,d.data_name,d.data_note,d.data_owner_id," 
			+"d.data_path,d.data_report_flag,d.data_type_id,dt.* from esp_data d,esp_datatype dt" 
			+" where d.DATA_FLAG='1' and dt.TYPE_ID=d.DATA_TYPE_ID ORDER BY d.DATA_OWNER_ID) b" 
			+" ON a.STU_ID=b.DATA_OWNER_ID WHERE a.stu_id IS NOT NULL ");
		if (userRoleId.equals(Role.ADMIN) || userRoleId.equals(Role.TEACHER)) {//如果不是学生
			//a
			if (stuId != null && !stuId.equals("")) {
				sqlBuffer.append(" and a.stu_id='"+stuId+"'");
			}
			if (userName != null && !userName.equals("")) {
				sqlBuffer.append(" and a.user_name='"+userName+"'");
			}
			//b
			if (dataReportFlag != null && !dataReportFlag.equals("")) {
				sqlBuffer.append(" and b.data_report_flag='"+dataReportFlag+"'");
			}
			if (typeId != null && !typeId.equals("")) {
				sqlBuffer.append(" and b.type_id='"+typeId+"'");
			}
			list = getJdbcTemplate().queryForList(sqlBuffer.toString());
		}else {//如果是学生
			sqlBuffer.append("and a.stu_id=?");
			list = getJdbcTemplate().queryForList(sqlBuffer.toString(), new Object[]{stuId});
		}
		logger.info("queryReportHead>>sqlBuffer:"+sqlBuffer);
		logger.info("queryReportHead>>list:"+list);
		return list;
	}
	@Override
	public List<Map<String,Object>> queryDataBodyB01(String stu_id){
		if (stu_id == null) {
			stu_id = "";
		}
		List<Map<String,Object>> list;
		sql = "SELECT d.data_id,d.data_path,d.data_name,d.data_owner_id,d.data_cretime,d.data_note FROM esp_data d" +
				" where d.DATA_FLAG='"+Data.STUDENT_DATA+"' and d.DATA_OWNER_ID=?";
		list = getJdbcTemplate().queryForList(sql, new Object[]{stu_id});
		logger.info("queryDataBodyB01>>sql:"+sql);
		logger.info("queryDataBodyB01>>list:"+list);
		return list;
	}
	@Override
	public List<Map<String,Object>> queryDataHeadB02(String dataName, String dataTypeId, String dataNote){//dataNote与task相关
		sqlBuffer = new StringBuffer("select data_id,data_name,data_type_id,data_cretime,data_path" +
				",data_owner_id,data_note,data_flag,data_report_flag from esp_data where data_flag='"+Data.TEACHER_DATA+"'" +
				" and data_report_flag not in('Y','N') ");
		if (dataName != null && !dataName.equals("")) {
			sqlBuffer.append(" and data_name='"+dataName+"'");
		}
		if (dataTypeId != null && !dataTypeId.equals("")) {
			sqlBuffer.append(" and data_type_id='"+dataTypeId+"'");
		}
		if (dataNote != null && !dataNote.equals("")) {
			sqlBuffer.append(" and data_note='"+dataNote+"'");
		}
		List<Map<String,Object>> list;
		list = getJdbcTemplate().queryForList(sqlBuffer.toString());
		logger.info("queryDataHeadB02>>sqlBuffer:"+sqlBuffer);
		logger.info("queryDataHeadB02>>list:"+list);
		return list;
	}
	@Override
	public int addData(String dataId, String dataName, String dataTypeId, Date dataCretime, String dataPath,
			 String dataOwnerId, String dataNote, String dataFlag, String dataReportFlag){
		int arr[] = IDFactory.getSqlTime();
		dataCretime = new Date(arr[0], arr[1], arr[2]);
		sql = "insert into esp_data" +
			" (data_id,data_name,data_type_id,data_cretime,data_path,data_owner_id,data_note,data_flag,data_report_flag)" +
			" values (?,?,?,?,?,?,?,?,?)";
		int temp = 0;
		temp = getJdbcTemplate().update(sql, new Object[]{dataId,dataName,dataTypeId,dataCretime,dataPath,
				dataOwnerId,dataNote,dataFlag,dataReportFlag});
		logger.info("addData>>sql:"+sql);
		logger.info("addData>>temp:"+temp);
		return temp;
	}
	@Override
	public int removeData(String dataId){
		boolean flag = this.deleteFile(dataId);
		int temp = 0;
		if (!flag) {//如果删除成功
			return 0;
		}
		sql = "delete from esp_data where data_id=?";
		temp = getJdbcTemplate().update(sql, new Object[]{dataId});
		logger.info("removeData>>sql:"+sql);
		logger.info("removeData>>temp:"+temp+"+flag:"+flag);
		return temp;
	}
	@Override
	public int updataData(String dataId, String dataName, String dataTypeId, Date dataCretime, String dataPath,
			 String dataOwnerId, String dataNote, String dataFlag, String dataReportFlag){
		int arr[] = IDFactory.getSqlTime();
		dataCretime = new Date(arr[0], arr[1], arr[2]);
		boolean flag = this.deleteFile(dataId);
		if (!flag) {
			return 0;
		}
		sql = null;
		sql = "UPDATE esp_data set " 
			+"data_name=?,data_type_id=?,data_cretime=?,data_path=?,data_owner_id=?," 
			+"data_note=?,data_flag=?,data_report_flag=? where data_id=?";
		int temp = 0;
		temp = getJdbcTemplate().update(sql, new Object[]{dataName,dataTypeId,dataCretime,dataPath,
				dataOwnerId,dataNote,dataFlag,dataReportFlag,dataId});
		logger.info("updataData>>sql:"+sql);
		logger.info("updataData>>temp:"+temp);
		return temp;
	}
//	 * =======================================================================================d02
	@Override
	public int addSurvey(String survey_id,String survey1,String survey2,String survey3,String survey4,String survey5
			,String survey6,String survey7,String survey8,String survey9){
		sql = "insert into esp_survey (survey_id,survey1,survey2,survey3,survey4,survey5" +
				",survey6,survey7,survey8,survey9) values (?,?,?,?,?,?,?,?,?,?)";
		int temp = 0;
		temp = getJdbcTemplate().update(sql, new Object[]{survey_id,survey1,survey2,survey3,survey4,
				survey5,survey6,survey7,survey8,survey9});
		logger.info("addSurvey>>sql:"+sql);
		logger.info("addSurvey>>temp:"+temp);
		return temp;
		
	}
	@Override
	public List<Map<String,Object>> querySurvey(String oneSurvey){
		sql = "select s."+ oneSurvey 
			+ ",CONCAT_WS('-',SUBSTR(s.SURVEY_ID,1,4),SUBSTR(s.SURVEY_ID,5,2),SUBSTR(s.SURVEY_ID,7,2)) survey_time" 
			+ " from esp_survey s where "+ oneSurvey +" is not null AND "+oneSurvey+" != '' order by survey_id";
		List<Map<String,Object>> list = getJdbcTemplate().queryForList(sql);
		logger.info("querySurvey>>sql:"+sql);
		logger.info("querySurvey>>list:"+list);
		return list;
	}
//	 * =======================================================================================e01
	@Override
	public List<Map<String,Object>> queryE01Head(String user_id,String user_role_id){
		List<Map<String,Object>> list;
		if (user_role_id.equals(Role.ADMIN) || user_role_id.equals(Role.TEACHER)) {
			sqlBuffer = new StringBuffer("SELECT  u.user_name,u.user_id,r.role_name,t.teacher_phone" +
					" user_phone,t.teacher_qq user_qq,t.teacher_eamil user_email,t.teacher_sex user_sex," +
					"t.teacher_note user_note,t.teacher_create_time user_cre_time from esp_user u,esp_teacher t,esp_role r" +
					" WHERE u.USER_ID=t.TEACHER_ID AND u.USER_ROLE_ID=r.ROLE_ID" +
					" AND u.USER_ID=?");
			
		}else {
			sqlBuffer = new StringBuffer("SELECT a.user_name,a.user_id,a.role_name,a.stu_phone" +
					" user_phone,a.stu_qq user_qq,a.stu_eamil user_email,a.stu_sex user_sex,a.stu_cre_time user_cre_time," +
					"a.stu_note user_note,a.faculty_name,a.major_name,g.group_name,a.stu_grade," +
					"a.stu_class FROM" +
					" (SELECT * from esp_user u,esp_student s,esp_faculty f,esp_major m,esp_role r" +
					" WHERE u.USER_ID=s.STU_ID AND u.USER_ROLE_ID=r.ROLE_ID AND" +
					" s.STU_FACULTY_ID=f.FACULTY_ID AND s.STU_MAJOR_ID=m.MAJOR_ID AND u.USER_ID=?) a" +
					" LEFT JOIN esp_group g ON a.STU_GROUP_ID=g.GROUP_ID");
		}
		list = getJdbcTemplate().queryForList(sqlBuffer.toString(), new Object[]{user_id});
		logger.info("queryE01Head>>sqlBuffer:"+sqlBuffer);
		logger.info("queryE01Head>>list:"+list);
		return list;
	}
	@Override
	public int updateE01Head(String user_id,String user_role_id,String user_sex,String user_note
			,String user_phone,String user_qq,String user_email){
		if (user_role_id.equals(Role.ADMIN) || user_role_id.equals(Role.TEACHER)) {
			sql = "update esp_teacher set teacher_sex=?,teacher_note=?,teacher_phone=?,teacher_qq=?," +
					"teacher_eamil=? where teacher_id=?";
		}else {
			sql = "update esp_student set stu_sex=?,stu_note=?,stu_phone=?,stu_qq=?,stu_eamil=? where stu_id=?";
		}
		int temp = 0;
		try {
			temp = getJdbcTemplate().update(sql, new Object[]{user_sex,user_note,user_phone,user_qq,user_email,user_id});
			logger.info("updateE01Head>>>>sql>>"+sql);
			logger.info("updateE01Head>>>>temp>>"+temp);
			return temp;
		} catch (Exception e) {
			logger.info("更改个人信息出错："+e.getMessage());
			return 0;
		}
		
	}
//	 * =======================================================================================e03
	@Override
	public int updatePassword(String oldPassword,String newPassword,String user_id){
		int temp = 0;
		if(user_id == null || user_id.equals("")){
			return temp;
		}
		int i = this.checkPsw(user_id, oldPassword);
		if (i == 1) {
			sql = "update esp_user set user_password=? where user_id=?";
			logger.info("updatePassword>>"+user_id+">>sql:"+sql);
			temp = getJdbcTemplate().update(sql, new Object[]{newPassword, user_id});
		}else {
			temp = 0;
			logger.info("updatePassword>>原密码输入错误");
		}
		return temp;
	}
	@Override
	public int checkPsw(String user_id, String oldPassword){
		int temp = 0;
		sql = "select count(*) from esp_user where user_id=? and user_password=?";
		temp = getJdbcTemplate().queryForInt(sql, new Object[]{user_id, oldPassword});
		return temp;
	}
//	 *=======================================================================================e02 
	@Override
	public List<Map<String,Object>> queryE02Head(String stu_id){
		if (stu_id == null || stu_id.trim().equals("")) {
			return null;
		}
		List<Map<String,Object>> list;
		sql = "SELECT a.group_id,a.group_name,t.task_id,t.task_name,a.group_cre_time,a.user_id,a.user_name,a.group_note" +
				" FROM (SELECT * FROM (SELECT * FROM esp_user u,esp_student s1 WHERE u.USER_ID=s1.STU_ID AND s1.STU_ID=?) s" +
				" LEFT JOIN esp_group g ON s.STU_GROUP_ID=g.GROUP_ID) a" +
				" LEFT JOIN esp_task t ON t.TASK_ID=a.group_task_id";
		logger.info("queryE02Head>>sql:"+sql);
		list = getJdbcTemplate().queryForList(sql, new Object[]{stu_id});
		sql = "SELECT COUNT(*) FROM esp_student WHERE STU_GROUP_ID IN (SELECT s.STU_GROUP_ID FROM esp_student s" +
				" WHERE s.stu_id=?)";
		int group_total = 0;
		group_total = getJdbcTemplate().queryForInt(sql,  new Object[]{stu_id});
		list.get(0).put("group_total", group_total);
		logger.info("queryE02Head>>sql:"+sql);
		logger.info("queryE02Head>>map:"+list);
		return list;
	}
//	 *=======================================================================================public方法 
	@Override
	public List<Map<String,Object>> queryAll(String tableName){
		List<Map<String,Object>> list;
		sql = "select * from "+tableName;
		list = getJdbcTemplate().queryForList(sql);
		return list;
	}
	@Override
	public List<Map<String,Object>> querySqlForList(String sql){
		if (sql.equals("") || sql == null) {
			return null;
		}
		return getJdbcTemplate().queryForList(sql);
	}
	@Override
	public int querySqlForInt(String sql){
		if (sql.equals("") || sql == null) {
			return 0;
		}
		return getJdbcTemplate().queryForInt(sql);
	}
	@Override
	public int updateSqlForInt(String sql){
		if (sql.equals("") || sql == null) {
			return 0;
		}
		try {
			getJdbcTemplate().update(sql);
			return 1;
		} catch (Exception e) {
			return 0;
		}
	}
}
